﻿<cfsilent>
	<cfscript>
		
		dictAdvice = getProperty("serviceFactory").getBean("senateDictionaryAdvice");
		timeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		
		termId = event.getArg("TermID");
		
		academicYear = event.getArg("AcademicYear");
		academicTerm = event.getArg("AcademicTerm");
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat("学期缴费清单." & academicYear & "-" & academicTerm, "utf-8");
		
		/* 生成电子表格 */
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 未缴费名单 */
		sql = "	SELECT
					stu.stu_id,stu.stu_name,clz.cls_name,clz.grade,
					sbj.sbj_name,
					i.institute_name,
					sc.st,sc.choose_time,sc.retaking,
					c.course_name,c.course_credit,
					sbj.sbj_id,
					sc.choose_prop
				FROM
					t_student_course sc INNER JOIN t_student stu ON stu.stu_id = sc.stu_id INNER JOIN t_class clz ON clz.cls_id = stu.cls_id
					INNER JOIN t_subject sbj ON sbj.sbj_id = clz.sbj_id INNER JOIN t_institute i ON i.institute_id = sbj.institute_id
					INNER JOIN t_course c ON c.cid = sc.cid
				WHERE
					SUBSTR(sc.choose_prop,2,2) = '00'
					AND
					sc.term_id = :termId 
				ORDER BY
					i.institute_id,sbj.sbj_id,clz.cls_id,stu.stu_id";
		
		queryObj = new Query(datasource = application.dnsSlave);
		queryObj.addParam(name = "termId", value = termId, cfsqltype = "cf_sql_char");
		rs_unCharged = queryObj.execute(sql = sql).getResult();
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("欠费清单", false);
				
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "学号,姓名,班级,年级,专业,学院,修读方式,选课时间,是否重修,课程,学分,学分单价,学费");
		
		row = 1;
		
		for( a = 1; a le rs_unCharged.recordCount; a++){
			
			row++;
			
			/* 正常专业学费 */
			sql = "	SELECT
						a.regular,a.resit
					FROM
						t_charge a
					WHERE
						EXISTS(
							SELECT
								0
							FROM
								t_student b
								INNER JOIN t_class clz ON b.cls_id = clz.cls_id
							WHERE
								a.grade = clz.grade
								AND
								a.sbj_id = clz.sbj_id
								AND
								b.stu_id = :studentId 
						)";
			
			queryObj = new Query(datasource = application.dnsSlave);
			queryObj.addParam(name="studentId", value=rs_unCharged["stu_id"][a], cfsqltype="cf_sql_varchar");
			rs_charge = queryObj.execute(sql = sql).getResult();
			
			/* 辅修专业学费 */
			sql = "	SELECT
						a.assist
					FROM
						t_charge a
					WHERE
						EXISTS(
							SELECT
								0
							FROM
								t_student_secsubject ss
							WHERE
								a.grade = ss.grade
								AND
								a.sbj_id = ss.sbj_id
								AND
								ss.stu_id = :studentId 
						)";
			
			queryObj = new Query(datasource = application.dnsSlave);
			queryObj.addParam(name="studentId", value=rs_unCharged["stu_id"][a], cfsqltype="cf_sql_varchar");
			rs_chargeAst = queryObj.execute(sql = sql).getResult();
			/* 双学位专业学费 */
			sql = "	SELECT
						a.secondary
					FROM
						t_charge a
					WHERE
						EXISTS(
							SELECT
								0
							FROM
								t_student_secdegree b
							WHERE
								a.grade = b.grade
								AND
								a.sbj_id = b.sbj_id
								AND
								b.stu_id = :studentId 
						)";
			
			queryObj = new Query(datasource = application.dnsSlave);
			queryObj.addParam(name="studentId", value=rs_unCharged["stu_id"][a], cfsqltype="cf_sql_varchar");
			rs_chargeSec = queryObj.execute(sql = sql).getResult();
			
			chargePerCredit = 0;
			chargeRetakenPerCredit = 0;
			chargeSecondaryPerCredit = 0;
			chargeAssistPerCredit = 0;
			
			thisCharge = 0;
			
			if ( rs_charge.recordCount ) {
				chargePerCredit = rs_charge.regular;
				chargeRetakenPerCredit = rs_charge.resit;
			}
			
			if ( rs_chargeAst.recordCount ) {
				chargeAssistPerCredit = rs_chargeAst.assist;
			}
			
			if ( rs_chargeSec.recordCount ) {
				chargeSecondaryPerCredit = rs_chargeSec.secondary;
			}
			
			/* 构建选课数据 */
			rowData = rs_unCharged["stu_id"][a]
					& "," & rs_unCharged["stu_name"][a]
					& "," & rs_unCharged["cls_name"][a]
					& "," & rs_unCharged["grade"][a]
					& "," & rs_unCharged["sbj_name"][a]
					& "," & rs_unCharged["institute_name"][a]
					& "," & dictAdvice.getStudyType( rs_unCharged["st"][a] )
					& "," & timeAdvice.formatDatetimeString( rs_unCharged["choose_time"][a], "MM月DD日 HH时MI分")
					& "," & dictAdvice.getBoolean( rs_unCharged["retaking"][a] )
					& "," & rs_unCharged["course_name"][a]
					& "," & numberFormat( rs_unCharged["course_credit"][a], "_.__")
					;
			
			/* 计算学生学费 */
			switch ( rs_unCharged["st"][a] ) {
				case "N":
				case "F":
				case "E":
					thisCharge = chargePerCredit;
					break;
				case "R":
					thisCharge = chargeRetakenPerCredit;
					break;
				case "S":
					thisCharge = chargeSecondaryPerCredit;
					break;
				case "A":
					thisCharge = chargeAssistPerCredit;
					break;
			}
			
			rowData = rowData 
						& "," & numberFormat( thisCharge, "_.__" )
						& "," & numberFormat( thisCharge * rs_unCharged["course_credit"][a] , "_.__" );
			
			
			/* 写入欠费信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
		}
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
		
	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />	

</cfsilent>